package com.fjnldx.mes.mapper.energy;


import com.fjnldx.mes.domain.energy.req.LSSJReq;
import com.fjnldx.mes.domain.energy.vo.KtKy_MemberVO;
import com.fjnldx.mes.domain.energy.vo.LSCX_VO;
import com.fjnldx.mes.domain.energy.vo.SSCX_GongXu_VO;
import com.fjnldx.mes.domain.energy.vo.SSCX_PJ_VO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
public interface EnergyMapper {

    /**
     * 插叙空调空压班耗电情况
     * @return
     */
    @Select("<script> select dt.devSn, dt.gongxu,dt.dthd, dt.zhd, db.dbhd,dy.dyhd, dz.dzhd ,pj.AvgU, pj.AvgI from  " +
            "            (select devSn, gongxu ,max(ImpEp) as zhd ,max(ImpEp) - min(ImpEp) as dthd from dian_table  " +
            "             where gongxu=#{gongxu}  and  time >DATE_FORMAT( NOW(), '%Y-%m-%d 00:00:00' )    " +
            "            GROUP BY devSn ) dt  " +
            "             left join " +
            "             (select devSn, max(ImpEp) - min(ImpEp) as dbhd from dian_table  " +
            "             WHERE gongxu=#{gongxu}  and time >IF(DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' )>now(),DATE_FORMAT( NOW(),'%Y-%m-%d 00:00:00'),DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00') ) " +
            "             GROUP BY devSn  ) db on dt.devSn=db.devSn  " +
            "             left join  " +
            "            (select devSn, sum(haodian) as dyhd from dian_day_summary  " +
            "            where gongxu=#{gongxu}  and  day >DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')   " +
            "            GROUP BY devSn ) dy on dy.devSn = dt.devSn  " +
            "             left join  " +
            "            (select devSn, sum(haodian) as dzhd from dian_day_summary  " +
            "            where gongxu=#{gongxu}  and  day >date_sub(curdate(),INTERVAL WEEKDAY(curdate())DAY) GROUP BY devSn) dz on dz.devSn = dt.devSn " +
            "             left join  " +
            "            (select z.devSn, " +
            "             Uavg as AvgU, Iavg as AvgI  " +
            "             from  " +
            "            dian_table z right join (select devSn , max(time) as time from dian_table  " +
            "            where gongxu=#{gongxu}   GROUP  BY devSn ) d on z.devSn = d.devSn and z.time = d.time ) pj on dt.devSn = pj.devSn " +
            "            </script>")
    public List<KtKy_MemberVO> queryKTKYHD(String gongxu);

    /**
     * 根据班次 查询 细沙工序下的品种耗电百分比
     * @param bc
     * @return
     */
    @Select("<script> " +
            "select sum(hd) as hd,IFNULL(pz,'其他') as pz ,'细纱' as gongxu from " +
            "   (select b.pz,a.hd ,a.devSn,a.gongxu from    " +
            "       (select  MAX(ImpEp)-min(ImpEp) as hd ,devSn,gongxu from dian_table where gongxu = '细纱' " +
            "<if test='bc == \"早班\"'>" +
            " and time &lt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            " <if test='bc == \"晚班\"'> " +
            "and time &gt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            " GROUP BY devSn ) a" +
            " LEFT join " +
            "   mes_newscjh b on a.devSn = b.devSn and a.gongxu = b.gongxu )c GROUP BY pz" +
            "</script>")

    List<SSCX_PJ_VO> queryXiShaByBC(String bc);

    /**
     * 根据班次 查询 络筒工序下的品种耗电百分比
     * @param bc
     * @return
     */
    @Select("<script> " +
            "select sum(hd) as hd,IFNULL(pz,'其他') as pz ,'络筒' as gongxu from " +
            "   (select b.pz,a.hd ,a.devSn,a.gongxu from    " +
            "       (select  MAX(ImpEp)-min(ImpEp) as hd ,devSn,'络筒'as gongxu from dian_table where gongxu = '自络' " +
            "<if test='bc == \"早班\"'>" +
            " and time &lt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            " <if test='bc == \"晚班\"'> " +
            "and time &gt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            " GROUP BY devSn ) a" +
            " LEFT join " +
            "   mes_newscjh b on a.devSn = b.devSn and a.gongxu = b.gongxu )c GROUP BY pz" +
            "</script>")
    List<SSCX_PJ_VO> queryLuoTongByBC(String bc);

    /**
     * 查询当天 班次下 各个工序的耗电值
     * @param bc
     * @return
     */
    @Select("<script>" +
            "select sum(a.dbhd) as hd,a.gongxu from( " +
            "" +
            "select gongxu,devSn ,max(ImpEp)-min(ImpEp) as dbhd from dian_table   " +
            "where 1=1 " +
            "<if test='bc == \"早班\"'>" +
            " and time &lt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            " <if test='bc == \"晚班\"'> " +
            "and time &gt; DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00' ) " +
            "</if>" +
            "GROUP BY devSn ,gongxu  " +
            ") a GROUP BY a.gongxu;"+
            "</script>")
    List<SSCX_GongXu_VO> getDayGongGXHD(String bc);


    @Select("select  gongxu,sum(haodian) as hd from dian_day_summary " +
            "where 1=1 and day BETWEEN #{start} and #{end} " +
            "GROUP BY gongxu ")
    List<SSCX_GongXu_VO> getDaySummaryGongGXHD(Date start, Date end);

    /**
     * 获取生产车间列表
     * @return
     */
    @Select("select concat(id,'--',cj) from mes_cejian")
    List<String> getSCCJ();

    /**
     * 获取工序列表
     * @return
     */
    @Select("select gongxu from dian_day_summary group by gongxu")
    List<String> getGongXu();

    /**
     * 查询历史数据
     * @param req
     * @return
     */
    @Select("<script>" +
            "select a.gongxu,a.devSn,a.haodian, b.pz,c.dl,c.cs,b.scjhbh as cj from " +
            "( select gongxu ,devSn, sum(haodian) as haodian  from dian_day_summary " +
            "where 1= 1" +
            " <if test='start != null'> and day &gt; #{start}  </if>"+
            " <if test='end != null'> and day &lt; #{end}  </if>"+
            " <if test='sn != null'>and devSn  = #{sn} </if>"+
            " <if test='gx != null'>and gongxu  = #{gx}  </if>"+
            " GROUP BY gongxu ,devSn)a " +
            " JOIN   " +
            " mes_newscjh b on a.gongxu = b.gongxu and a.devSn = b.devSn " +
            " <if test='cj != null'> and b.scjhbh = #{cj}  </if> " +
            " left JOIN   " +
            " mes_dinger c on a.gongxu = c.gongxu and  b.pz = c.pz  limit #{page},#{pageSize}" +
            "</script>")

    List<LSCX_VO> queryHistory(LSSJReq req);

    /**
     * 查询历史数据总行数
     * @param req
     * @return
     */

    @Select("<script>" +
            "select count(1) from " +
            "( select gongxu ,devSn, sum(haodian) as haodian  from dian_day_summary " +
            "where 1= 1" +
            " <if test='start != null'> and day &gt; #{start}  </if>"+
            " <if test='end != null'> and day &lt; #{end}  </if>"+
            " <if test='sn != null'>and devSn  = #{sn} </if>"+
            " <if test='gx != null'>and gongxu  = #{gx}  </if>"+
            " GROUP BY gongxu ,devSn)a " +
            " JOIN   " +
            " mes_newscjh b on a.gongxu = b.gongxu and a.devSn = b.devSn " +
            " <if test='cj != null'> and b.scjhbh = #{cj}  </if> " +
            " left JOIN   " +
            " mes_dinger c on a.gongxu = c.gongxu and  b.pz = c.pz  " +
            "</script>")
    Integer queryHistoryCount(LSSJReq req);
}

